1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmServicesRecord1
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and S_ID not in (Select ServiceID from InvoiceInfo1) and S_ID not in (Select ServiceID from InvoiceInfo1) order by ServiceCreationDate", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 fillServiceCode()
25 End Sub
26
27 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
28 Try
29 If dgw.Rows.Count > 0 Then
30 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
31 frmBilling1.Show()
32 Me.Hide()
33 frmBilling1.txtS_ID.Text = dr.Cells(0).Value.ToString()
34 frmBilling1.txtServiceCode.Text = dr.Cells(1).Value.ToString()
35 frmBilling1.txtCustomerID.Text = dr.Cells(4).Value.ToString()
36 frmBilling1.txtCID.Text = dr.Cells(3).Value.ToString()
37 frmBilling1.txtCustomerName.Text = dr.Cells(5).Value.ToString()
38 frmBilling1.txtRepairCharges.Text = dr.Cells(9).Value.ToString()
39 frmBilling1.txtUpfront.Text = dr.Cells(10).Value.ToString()
40 con = New SqlConnection(cs)
41 con.Open()
42 Dim ct As String = "select RTRIM(ContactNo) from Customer where ID=" & dr.Cells(3).Value & ""
43 cmd = New SqlCommand(ct)
44 cmd.Connection = con
45 rdr = cmd.ExecuteReader()
46 If rdr.Read Then
47 frmBilling1.txtContactNo.Text = rdr.GetValue(0)
48 If Not rdr Is Nothing Then
49 rdr.Close()
50 End If
51 Exit Sub
52 End If
53 con.Close()
54 End If
55 Catch ex As Exception
56 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
57 End Try
58 End Sub
59
60 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
61 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
62 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
63 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
64 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
65 End If
66 Dim b As Brush = SystemBrushes.ControlText
67 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
68
69 End Sub
70 Sub fillServiceCode()
71 Try
72 con = New SqlConnection(cs)
73 con.Open()
74 adp = New SqlDataAdapter()
75 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(ServiceCode) FROM Service", con)
76 ds = New DataSet("ds")
77 adp.Fill(ds)
78 dtable = ds.Tables(0)
79 cmbServiceCode.Items.Clear()
80 For Each drow As DataRow In dtable.Rows
81 cmbServiceCode.Items.Add(drow(0).ToString())
82 Next
83 Catch ex As Exception
84 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
85 End Try
86 End Sub
87 Sub Reset()
88 cmbServiceCode.Text = ""
89 txtCustomerName.Text = ""
90 fillServiceCode()
91 dtpDateFrom.Text = Today
92 dtpDateTo.Text = Today
93 DateTimePicker2.Text = Today
94 DateTimePicker1.Text = Today
95 cmbStatus.SelectedIndex = -1
96 Getdata()
97 End Sub
98 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
99 Reset()
100 End Sub
101
102 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
103 Me.Close()
104 End Sub
105
106
107 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
108 Dim rowsTotal, colsTotal As Short
109 Dim I, j, iC As Short
110 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
111 Dim xlApp As New Excel.Application
112 Try
113 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
114 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
115 xlApp.Visible = True
116
117 rowsTotal = dgw.RowCount
118 colsTotal = dgw.Columns.Count - 1
119 With excelWorksheet
120 .Cells.Select()
121 .Cells.Delete()
122 For iC = 0 To colsTotal
123 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
124 Next
125 For I = 0 To rowsTotal - 1
126 For j = 0 To colsTotal
127 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
128 Next j
129 Next I
130 .Rows("1:1").Font.FontStyle = "Bold"
131 .Rows("1:1").Font.Size = 12
132
133 .Cells.Columns.AutoFit()
134 .Cells.Select()
135 .Cells.EntireColumn.AutoFit()
136 .Cells(1, 1).Select()
137 End With
138 Catch ex As Exception
139 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
140 Finally
141 'RELEASE ALLOACTED RESOURCES
142 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
143 xlApp = Nothing
144 End Try
145 End Sub
146
147 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
148 Try
149 con = New SqlConnection(cs)
150 con.Open()
151 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and S_ID not in (Select ServiceID from InvoiceInfo1) and ServiceCreationDate between @d1 and @d2 order by ServiceCreationDate", con)
152 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
153 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
154 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
155 dgw.Rows.Clear()
156 While (rdr.Read() = True)
157 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
158 End While
159 con.Close()
160 Catch ex As Exception
161 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
162 End Try
163 End Sub
164
165 Private Sub cmbOrderNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbServiceCode.SelectedIndexChanged
166 Try
167 con = New SqlConnection(cs)
168 con.Open()
169 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and S_ID not in (Select ServiceID from InvoiceInfo1) and ServiceCode='" & cmbServiceCode.Text & "' order by ServiceCreationDate", con)
170 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
171 dgw.Rows.Clear()
172 While (rdr.Read() = True)
173 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
174 End While
175 con.Close()
176 Catch ex As Exception
177 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
178 End Try
179 End Sub
180
181 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
182 Try
183 If cmbStatus.Text = "" Then
184 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
185 cmbStatus.Focus()
186 Exit Sub
187 End If
188 con = New SqlConnection(cs)
189 con.Open()
190 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and S_ID not in (Select ServiceID from InvoiceInfo1) and ServiceCreationDate between @d1 and @d2 and Status='" & cmbStatus.Text & "' order by ServiceCreationDate", con)
191 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
192 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value.Date
193 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
194 dgw.Rows.Clear()
195 While (rdr.Read() = True)
196 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9))
197 End While
198 con.Close()
199 Catch ex As Exception
200 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
201 End Try
202 End Sub
203
204 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
205 Try
206 con = New SqlConnection(cs)
207 con.Open()
208 cmd = New SqlCommand("Select S_ID, RTRIM(ServiceCode),ServiceCreationDate, Customer.ID,RTRIM(Customer.CustomerID),RTRIM(Name), RTRIM(ServiceType), RTRIM(ItemDescription), RTRIM(ProblemDescription), ChargesQuote, AdvanceDeposit, EstimatedRepairDate,RTRIM(Status), RTRIM(Service.Remarks) from Customer,Service where Customer.ID=Service.CustomerID and S_ID not in (Select ServiceID from InvoiceInfo1) and Name like '%" & txtCustomerName.Text & "%' order by ServiceCreationDate", con)
209 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
210 dgw.Rows.Clear()
211 While (rdr.Read() = True)
212 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
213 End While
214 con.Close()
215 Catch ex As Exception
216 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
217 End Try
218 End Sub
219
220 Private Sub cmbInvoiceNo_Format(sender As System.Object, e As System.Windows.Forms.ListControlConvertEventArgs) Handles cmbServiceCode.Format
221 If (e.DesiredType Is GetType(String)) Then
222 e.Value = e.Value.ToString.Trim
223 End If
224 End Sub
225 End Class
